import os
import xlrd


def excel_to_json(file_path, *only_file_name):
    """
    excel转json
    :param file_path: 文件存储路径
    :return: json数据
    """
    files_list = get_all_file(file_path)
    all_sheet_data_list = list()
    for file in files_list:
        wb = xlrd.open_workbook(f'{file_path}\\{file}')
        # 所有页签
        sh_sheets = wb.sheets()
        # 页签遍历
        for sheet in sh_sheets:
            # print("sheet名", sheet.name)
            if sheet.name[0] != "#" and sheet.name in only_file_name:
                # print("sheet名", sheet.name)
                id_flag = 0
                # 行遍历
                row_num_list = list()
                sheet_all_data_dict = dict()
                for row_num in range(4, sheet.nrows):
                    row_values = sheet.row_values(row_num)
                    # 行中的元素遍历
                    dict_data = dict()
                    for item_num in range(0, len(row_values)):
                        # id空用上一行代替
                        if row_values[0] == "" or row_values[0] == 0:
                            row_values[0] = id_flag
                        id_flag = row_values[0]
                        # 有效列遍历，排除""列
                        for index in range(0, len(sheet.row_values(2))):
                            # 跳过""列
                            if sheet.row_values(2)[index] == "" or sheet.row_values(2)[index] == 0:
                                continue
                            # 列赋值
                            if item_num == index:
                                if type(row_values[item_num]) == float:
                                    dict_data[sheet.row_values(2)[index]] = int(row_values[item_num])
                                else:
                                    dict_data[sheet.row_values(2)[index]] = row_values[item_num]
                    row_num_list.append(dict_data)
                    sheet_all_data_dict[sheet.name] = row_num_list
                all_sheet_data_list.append(sheet_all_data_dict)
    # j = json.dumps(all_sheet_data_list, ensure_ascii=False)
    return all_sheet_data_list


def get_all_file(dir_path):
    """
    过滤文件
    :param dir_path: 文件存储路径
    :return: 文件列表
    """
    files = os.listdir(dir_path)
    files_list = list()
    for name in files:
        if os.path.isfile(dir_path + "/" + name) and name[0] != "~" and name[-5:] == ".xlsx":
            files_list.append(name)
    return files_list


if __name__ == "__main__":
    path = r"E:\ocean_config\trunk\Excels"
    all_json = excel_to_json(path, "BattleActionConfig", "BattleTypeConfig")
    print(all_json)
    pass
